Promise = require 'bluebird'
_ = require 'lodash'
mysql = require 'mysql'
PoolConnection = require 'mysql/lib/PoolConnection'

log = require('../log')
config = require('../config')
mysqlConfig = config.mysql
Meta = require '../meta/Meta'

if mysqlConfig
    pool = mysql.createPool
        connectionLimit: 3
        host: mysqlConfig.host
        user: mysqlConfig.user
        password: mysqlConfig.password
        database: mysqlConfig.database

    pEndPool = Promise.promisify pool.end.bind(pool)
    pConnection = Promise.promisify pool.getConnection.bind(pool)
    exports.pConnection = pConnection

pQuery = Promise.promisify PoolConnection.prototype.query
pBeginTransaction = Promise.promisify PoolConnection.prototype.beginTransaction
pCommit = Promise.promisify PoolConnection.prototype.commit
pRollback = Promise.promisify PoolConnection.prototype.rollback

arrayToSelectClause = (array)->
    fields = (mysql.escapeId(f) for f in array)
    fields.join(',')

objectToWhereClause = (object, values)->
    conditions = []
    for key, value of object
        conditions.push "#{mysql.escapeId(key)} = ?"
        values.push value
    conditions.join ' AND '

criteriaToWhereClause = (criteria, sqlValues)->
    if criteria.relation
        items = []
        for item in criteria.items
            i = criteriaToWhereClause item, sqlValues
            items.push i if i
        return null unless items.length
        if criteria.relation == 'or'
            "(" + items.join(" OR ") + ")"
        else
            items.join(" AND ")
    else if criteria.field
        operator = criteria.operator
        comparedValue = criteria.value
        field = mysql.escapeId(criteria.field)
        switch operator
            when '=='
                sqlValues.push comparedValue # TODO 对于部分运算符要检查 comparedValue 不为 null/undefined/NaN
                field + " = ?"
            when '!='
                sqlValues.push comparedValue
                field + " <> ?"
            when '>'
                sqlValues.push comparedValue
                field + " > ?"
            when '>='
                sqlValues.push comparedValue
                field + " >= ?"
            when '<'
                sqlValues.push comparedValue
                field + " < ?"
            when '<='
                sqlValues.push comparedValue
                field + " <= ?"
            when 'in'
                field + " IN " + buildInClause(comparedValue, sqlValues)
            when 'nin'
                field + " NOT IN " + buildInClause(comparedValue, sqlValues)
            when 'start'
                sqlValues.push(comparedValue + "%")
                field + " LIKE ?"
            when 'end'
                sqlValues.push("%" + comparedValue)
                field + " LIKE ?"
            when 'contain'
                sqlValues.push("%" + comparedValue + "%")
                field + " LIKE ?"
            else
                null

buildInClause = (inList, sqlValues)->
    return "" unless inList?.length
    placeholders = []
    for i in inList
        placeholders.push "?"
        sqlValues.push i
    "(" + placeholders.join(',') + ")"

objectToOrderClause = (object)->
    orders = []
    for key, value of object
        orders.push mysql.escapeId(key) + " " + (value && "DESC" || "ASC")
    orders.join(',')

objectToSetClause = (object, values)->
    set = []
    for key, value of object
        set.push "#{mysql.escapeId(key)} = ?"
        values.push value
    set.join(',')

_listCriteriaFields = (criteria, list)->
    if criteria.relation
        for item in criteria.items
            _listCriteriaFields(criteria, list)
    else if criteria.field
        list.push criteria.field

listCriteriaFields = (criteria)->
    list = []
    _listCriteriaFields(criteria, list)
    _.uniq(list)

class EnhancedConnection
    constructor: (@conn)-> true

    pRead: -> pQuery.apply @conn, arguments
    pWrite: ->
        @written = true
        pQuery.apply @conn, arguments
    pBeginTransaction: -> pBeginTransaction.apply @conn, arguments
    pCommit: -> pCommit.apply @conn, arguments
    pRollback: -> pRollback.apply @conn, arguments

    release: -> @conn.release()

    gFind: ({table, criteria, includedFields, sortCriteria, pageNo, pageSize, paging})->
        values = []
        select = '*'
        where = ''
        orderBy = ''
        skipLimit = ''

        if includedFields
            # 需要加上在 where 子句中出现的列
            criteriaFields = listCriteriaFields criteria
            includedFields = _.uniqu(_.concat(includedFields, criteriaFields))
            select = arrayToSelectClause includedFields

        if criteria and _.size(criteria)
            _w = criteriaToWhereClause(criteria, values)
            where = "WHERE " + _w if _w

        if sortCriteria
            orderBy = "ORDER BY " + objectToOrderClause(sortCriteria)

        if pageSize > 0
            pageNo = 1 if pageNo < 1
            skipLimit = "SKIP #{(pageNo - 1) * pageSize} LIMIT #{pageSize}"

        sql = "select #{select} from #{table} #{where} #{orderBy} #{skipLimit}"
        list = yield @pRead(sql, values)

        unless paging
            list
        else
            sql = "select COUNT(1) as count from #{table} #{where}"
            r = yield @pRead(sql, values)
            {total: r[0].count, page: list}

    gListByIds: (table, ids)->
        return [] unless ids?.length

        sqlValues = []
        inClause = buildInClause(ids, sqlValues)
        return [] unless inClause

        sql = "select * from #{table} where _id IN #{inClause}"
        yield @pRead(sql, sqlValues)

    gInsertOne: (table, object, keys)->
        yield from @gInsertMany table, [object], keys

    gInsertMany: (table, objects, keys)->
        return null unless objects?.length
        unless keys?.length
            keys = _.keys(objects[0])
        return null unless keys?.length

        columns = (mysql.escapeId(key) for key in keys)

        placeholders = []
        sqlValues = []
        for object in objects
            placeholders2 = []
            for key in keys
                placeholders2.push '?'
                sqlValues.push object[key]
            placeholders.push "(#{placeholders2.join(',')})"

        sql = "insert into #{table}(#{columns.join(',')}) values #{placeholders.join(',')}"
        log.debug "sql,values", sql, sqlValues
        yield @pWrite(sql, sqlValues)

    gUpdateByObject: (table, criteriaObject, patch)->
        return null unless _.size(criteriaObject)
        return null unless _.size(patch)

        sqlValues = []
        set = objectToSetClause(patch, sqlValues)
        where = objectToWhereClause(criteriaObject, sqlValues)

        sql = "update #{table} set #{set} where #{where}"
        log.debug "sql,values", sql, sqlValues
        yield @pWrite(sql, sqlValues)

    gDeleteManyByIds: (table, ids)->
        return unless ids?.length

        sqlValues = []
        inClause = buildInClause ids, sqlValues
        sql = "delete * from #{table} where _id IN #{inClause}"
        yield @pWrite(sql, sqlValues)

gConnect = ->
    connection = yield exports.pConnection()
    new EnhancedConnection(connection)

exports.gConnect = gConnect

autoCommit = (work)->
    return (args...)->
        conn = yield from gConnect()
        try
            yield conn.pBeginTransaction()
            args.splice(0, 0, conn)
            r = yield from work(args...)
            yield conn.pCommit()
            return r
        catch e
            try
                yield conn.pRollback()
            catch e2
                log.system.error e2, "autoCommit, rollback"
            throw e
        finally
            conn.release()

exports.gUse = (work)->
    conn = yield from gConnect()
    try
        yield conn.pBeginTransaction()
        r = yield from work(conn)
        if conn.written
            yield conn.pCommit()
        return r
    catch e
        try
            if conn.written
                yield conn.pRollback()
        catch e2
            log.system.error e2, "using, rollback"
        throw e
    finally
        conn.release()

exports.gConnIfNeed = (entityMeta, ctx, work)->
    if entityMeta.db == Meta.DB.mysql
        yield from exports.gUse (conn)->
            ctx.conn = conn
            yield from work()
    else
        yield from work()

exports.gEnd = ->
    try
        yield pEndPool()
    catch e
        log.system.error e, 'end mysql'

exports.isIndexConflictError = (e)-> e.code == 'ER_DUP_ENTRY'

test = ->
    values = []
    criteria1 = {
        relation: 'and',
        items: [
            {operator: '==', field: 'name', value: "yy"},
            {operator: '==', field: 'age', value: 10}
            {operator: 'in', field: 'tag', value: ["gj", "lk"]},
            {
                relation: 'or',
                items: [{operator: '==', field: 'full', value: true}, {operator: '==', field: '`pk;', value: true}]
            }
        ]
    }
    criteria2 = {operator: '==', field: 'name', value: "yy"}
    criteria3 = {operator: '==', field: 'name', value: "yy", not: true}
    where = criteriaToWhereClause criteria1, values
    console.log where
    console.log values.join(", ")